package abandonedDogs;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import abandonedDogs.DogService;
import abandonedDogs.DogVO;

public class DogDAO implements DogService {
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String insertSQL = "insert into Dog( id, dname, sex, type, age, regdate, collar, fno, specificloc, storeddept, etc, mercykilling ) values ( id.nextval, ?, ?, ?, ?, sysdate, ?, ?, ?, ?, ?, ? )";
	private static final String selectSQL = "select id, dname, sex, regdate from Dog order by id asc";
	private static final String selecOneSQL = "select * from Dog where id = ?";
	private static final String updateSQL = "update Dog set id = id.nextval, dname = ?, sex = ?, type = ?, age = ?, regdate = sysdate, collar = ?, fno = ?, specificloc = ?, storeddept = ?, etc = ?, mercykilling = ? where id = ?";
	private static final String deleteSQL = "delete from Dog where id = ?";
	
	@Override
	public int insertDog(DogVO dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, dg.getDname());
				ps.setString(2, dg.getSex());
				ps.setInt(3, dg.getType());
				ps.setInt(4, dg.getAge());
				ps.setInt(5, dg.getCollar());
				ps.setInt(6, dg.getFno());
				ps.setString(7, dg.getSpecificloc());
				ps.setInt(8, dg.getStoreddept());
				ps.setString(9, dg.getEtc());
				ps.setString(10, dg.getMercykilling());
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		
		
		return cnt;
	}
	@Override
	public int updateDog(DogVO dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, dg.getDname());
				ps.setString(2, dg.getSex());
				ps.setInt(3, dg.getType());
				ps.setInt(4, dg.getAge());
				ps.setInt(5, dg.getCollar());
				ps.setInt(6, dg.getFno());
				ps.setString(7, dg.getSpecificloc());
				ps.setInt(8, dg.getStoreddept());
				ps.setString(9, dg.getEtc());
				ps.setString(10, dg.getMercykilling());
				ps.setString(11, dg.getId());
			
		}catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}
	@Override
	public int deleteDog(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}
	@Override
	public String selectDog() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				for (int i = 1; i <= 4; ++i) {
					result += rs.getString(i);
				}
				result += "\n";
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) {
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		return result;
	}
	@Override
	public String selectDog(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selecOneSQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				for(int i = 1; i <= 12; ++i) {
					result += rs.getString(i) + " ";				}
			}
			result += "\n";
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) {
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return result;
	}

}








